<?php


namespace app\service;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\cell\Coordinate;

class Excel
{
    public function uploadFieldExcel(string $file_url)
    {
//        $file_url = $this->request->param('file_url');  //文件路径
        if (!empty($file_url)) {
            $filePath = $file_url;
            //如果文件存在
            if (file_exists($filePath)) { //检查文件是否存在
                $exts = pathinfo($filePath, PATHINFO_EXTENSION);  //文件类型
                // 有Xls和Xlsx格式两种
                if ($exts == 'xlsx') {
                    $objReader = IOFactory::createReader('Xlsx');
                } else {
                    //$objReader = IOFactory::createReader('Xls');
                   return re_error_json(msg_code(21)); //请使用'Xlsx'格式文件进行带图片上传,否则图片上传失败！
                }
                $imageFilePath1 = root_path() . '/public/upload/';//图片保存目录
                $imageFilePath2 = 'admin/images/' . date("Ymd") . '/';
                $imageFilePath = $imageFilePath1 . $imageFilePath2;
                if (!file_exists($imageFilePath)) {
                    mkdir("$imageFilePath", 0777, true);
                }
                $objSpreadsheet = $objReader->load($filePath);
                $objWorksheet = $objSpreadsheet->getActiveSheet(0);  //getSheet(0)
                $highestRow = $objWorksheet->getHighestRow(); // 取得总行数
                $data = $objWorksheet->toArray();
                //$drawing 为 PhpOffice\PhpSpreadsheet\Worksheet\Drawing类的实例;   仅仅支持xlsx格式文件
                foreach ($objWorksheet->getDrawingCollection() as $drawing) {
                    list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());
                    $imageFileName = $drawing->getIndexedFilename(); //获取文件名称
                    switch ($drawing->getExtension()) {
                        case 'jpg':
                        case 'jpeg':
                            // $imageFileName .= '.jpg';
                            $source = imagecreatefromjpeg($drawing->getPath());
                            imagejpeg($source, $imageFilePath . $imageFileName);
                            break;
                        case 'gif':
                            $imageFileName .= '.gif';
                            $source = imagecreatefromgif($drawing->getPath());
                            imagegif($source, $imageFilePath . $imageFileName);
                            break;
                        case 'png':
                            $imageFileName .= '.png';
                            $source = imagecreatefrompng($drawing->getPath());
                            imagepng($source, $imageFilePath . $imageFileName);
                            break;
                    }
                    $startColumn = $this->ABC2decimal($startColumn);
                    $data[$startRow - 1][$startColumn] = $imageFilePath2 . $imageFileName;
                }
                //从第二行开始
                for ($i = 1; $i <= $highestRow - 1; $i++) {
                    $add_data[$i]['name'] = $data[$i][0];
                    $add_data[$i]['image'] = $data[$i][1];
                }
                dump($data);die;
//                $success_count = Db::name('test')->insertAll($add_data);
//                //unlink($filePath);  //删除文件
//                //判断导入成功数量
//                if ($success_count == $highestRow - 1) {
//                    $info = '导入成功！本次成功导入数量：' . $success_count . '条';
//                    $this->success($info);
//                } else {
//                    $info = '导入成功！本次成功导入字段数量：' . $success_count . '条,无效数据或已重复上传' . ($highestRow - 1) - $success_count . '条,与目标数不符！';
//                    $this->success($info);
//                }
            } else {
                $info = "文件不存在,文件上传失败！";
//                $this->error(['code' => 0, 'msg' => $info]);
            }
        } else {
            $info = "请上传文件！";
            $this->error(['code' => 0, 'msg' => $info]);
        }
    }

    /**
     * @param $abc
     * Desc: 字母序列化数字
     * User: 苏鹏
     * Date: 2023/10/7
     * Time: 15:44
     * @return float|int
     */
    public function ABC2decimal($abc)
    {
        $ten = 0;
        $len = strlen($abc);
        for ($i = 1; $i <= $len; $i++) {
            $char = substr($abc, 0 - $i, 1);//反向获取单个字符
            $int = ord($char);
            $ten += ($int - 65) * pow(26, $i - 1);
        }
        return $ten;
    }
}